﻿using System;
using System.Collections.Generic;
using System.Data;
using Dapper;

namespace Exhort.Dapper
{
    /// <summary>
    /// 数据Dapper工具
    /// </summary>
    public class DapperContext : IDisposable
    {
        /// <summary>
        /// 数据连接
        /// </summary>
        private IDbConnection _dbConnection { get; set; }

        /// <summary>
        /// 数据事务
        /// </summary>
        private IDbTransaction _dbTransaction { get; set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbConnecttion">连接对象</param>
        public DapperContext(IDbConnection dbConnecttion)
        {
            _dbConnection = dbConnecttion;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="providerName">提供商名称</param>
        public DapperContext(string connString, string providerName)
        {
            _dbConnection = DbFactory.GetDbConnection(connString, providerName);

            _dbConnection.Open();
        }

        /// <summary>
        /// 设置事务
        /// </summary>
        /// <param name="dbTransaction">事务对象</param>
        public void BeginTransaction(IDbTransaction dbTransaction)
        {
            _dbTransaction = dbTransaction;
        }

        /// <summary>
        /// 私有事务
        /// </summary>
        public void BeginTransaction()
        {
            _dbTransaction = _dbConnection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            if (_dbTransaction != null)
            {
                _dbTransaction.Commit();
            }
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            if (_dbTransaction != null)
            {
                _dbTransaction.Rollback();
            }
        }

        #region 扩展函数

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="columns"></param>
        /// <param name="order"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <param name="count"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public IEnumerable<T> GetPage<T>(string sql, string columns, string order, int rows, int page, out int count, object param = null, bool buffered = true, int? commandTimeout = null) where T : class
        {
            string _sql = string.Empty;

            count = ExecuteScalar<int>(string.Format(@"SELECT COUNT(*) FROM ({0}) AS A", sql), param, commandTimeout);

            if (!page.Equals(1))
            {
                _sql = string.Format(@"
                    WITH RECORDS AS(
                        SELECT TOP {0} {1},ROWNUM=ROW_NUMBER() OVER(ORDER BY {3}) 
                        FROM ({2}) AS A)
                    SELECT * FROM RECORDS
                    WHERE (ROWNUM BETWEEN {4} AND {5}) ORDER BY ROWNUM ASC",
                    rows * page,
                    columns,
                    sql,
                    order,
                    rows * (page - 1) + 1,
                    rows * page);
            }
            else
            {
                _sql = string.Format(@"SELECT TOP {0} {1} FROM ({2}) AS A ORDER BY {3}", rows.ToString(), columns, sql, order);
            }

            return Query<T>(_sql, param, buffered, commandTimeout);
        }

        #endregion

        #region 原生函数

        /// <summary>
        /// 根据SQL查询列表
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">SQL</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>查询结果泛型序列</returns>
        public IEnumerable<T> Query<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null)
        {
            return _dbConnection.Query<T>(sql, param, _dbTransaction, buffered, commandTimeout, CommandType.Text);
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql">SQL</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>受影响行数</returns>
        public int Execute(string sql, object param = null, int? commandTimeout = null)
        {
            return _dbConnection.Execute(sql, param, _dbTransaction, commandTimeout, CommandType.Text);
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, object param = null, int? commandTimeout = null)
        {
            return _dbConnection.ExecuteScalar(sql, param, _dbTransaction, commandTimeout, CommandType.Text);
        }

        /// <summary>
        /// 查询取值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="sql">查询字符串</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, object param = null, int? commandTimeout = null)
        {
            return _dbConnection.ExecuteScalar<T>(sql, param, _dbTransaction, commandTimeout, CommandType.Text);
        }

        /// <summary>
        /// 执行存储过程返回列表
        /// </summary>
        /// <param name="name">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <param name="buffered">是否缓冲</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns>查询结果泛型序列</returns>
        public IEnumerable<T> StoredQuery<T>(string name, object param = null, bool buffered = true, int? commandTimeout = null)
        {
            return _dbConnection.Query<T>(name, param, _dbTransaction, buffered, commandTimeout, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 存储过程取值
        /// </summary>
        /// <param name="name">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public object StoredScalar(string name, object param = null, int? commandTimeout = null)
        {
            return _dbConnection.ExecuteScalar(name, param, _dbTransaction, commandTimeout, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 存储过程取值
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="name">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public T StoredScalar<T>(string name, object param = null, int? commandTimeout = null)
        {
            return _dbConnection.ExecuteScalar<T>(name, param, _dbTransaction, commandTimeout, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="name">存储过程名称</param>
        /// <param name="param">参数</param>
        /// <param name="commandTimeout">超时时间</param>
        public void StoredExecute(string name, object param = null, int? commandTimeout = null)
        {
            _dbConnection.Execute(name, param, _dbTransaction, commandTimeout, CommandType.StoredProcedure);
        }

        #endregion

        #region 释放资源

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true); GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing">是否释放</param>
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (_dbTransaction != null) { try { _dbTransaction.Dispose(); _dbTransaction = null; } catch { } }
                if (_dbConnection != null) { try { _dbConnection.Dispose(); _dbConnection = null; } catch { } }
            }
        }

        #endregion
    }
}
